package com.ljw.community.service;

import com.ljw.community.entity.Topic;
import com.ljw.community.entity.User;
import org.apache.ibatis.jdbc.SQL;


public class SqlService {

    //user表insert操作SQL
    public String insertUserProvider(User user) {
        SQL sql = new SQL();
        sql.INSERT_INTO("user");
        sql.INTO_COLUMNS("id", "login_name", "password", "create_time");
        sql.INTO_VALUES("#{id}", "#{loginName}", "#{password}", "#{createTime}");
        if (user.getAddress() != null) {
            sql.INTO_COLUMNS("address");
            sql.INTO_VALUES("#{address}");
        }
        if (user.getTel() != null) {
            sql.INTO_COLUMNS("tel");
            sql.INTO_VALUES("#{tel}");
        }
        if (user.getName() != null) {
            sql.INTO_COLUMNS("name");
            sql.INTO_VALUES("#{name}");
        }
        if (user.getState() != null) {
            sql.INTO_COLUMNS("state");
            sql.INTO_VALUES("#{state}");
        }
        if (user.getDelFlag() != null) {
            sql.INTO_COLUMNS("del_flag");
            sql.INTO_VALUES("#{delFlag}");
        }

        return sql.toString();
    }

    //topic表loadTopics操作SQL
    public String loadTopicsProvider(String tag) {
        SQL sql = new SQL();
        sql.SELECT("t.*,u.login_name creator_login_name,tag.tag_name");
        sql.FROM("topic t,user u,tag");
        sql.WHERE(" t.creator_id = u.id AND t.tag_id=tag.id and t.state = '0' and t.del_flag = '0'");
        if (tag != null && !tag.isEmpty() && !tag.equals("全部")) {
            sql.WHERE("tag.tag_name= #{tag}");
        }
        sql.ORDER_BY("create_time desc,update_time desc");
        return sql.toString();

    }

    //topic表loadMyTopics操作SQL
    public String loadMyTopicsProvider(String date, String loginName) {
        SQL sql = new SQL();
        sql.SELECT("t.*,u.login_name creator_login_name,tag.tag_name");
        sql.FROM("topic t,user u,tag");
        sql.WHERE(" t.creator_id = u.id AND t.tag_id=tag.id and t.del_flag = '0'");
        sql.WHERE(" t.creator_id = (SELECT id from user where login_name = #{loginName})");
        if (date != null && !date.isEmpty()) {
            sql.WHERE("date_format(t.create_time,'%Y-%m-%d') = STR_TO_DATE(#{date},'%Y-%m-%d')");
        }
        sql.ORDER_BY("create_time desc,update_time desc");
        return sql.toString();

    }

    //topic表loadFollowedTopics操作SQL
    public String loadFollowedTopicsProvider(String tag, String loginName) {
        SQL sql = new SQL();
        sql.SELECT("t.*,u.login_name creator_login_name,tag.tag_name");
        sql.FROM("topic t,user u,tag");
        sql.WHERE("t.creator_id = u.id AND t.tag_id=tag.id");
        sql.WHERE("t.id in (SELECT f.topic_id FROM followed_topic f where creator_id = (select id from user where " +
                "login_name=#{loginName}) AND f.del_flag='0' )");
        if (tag != null && !tag.isEmpty() && !tag.equals("全部")) {
            sql.WHERE("tag.tag_name = #{tag}");
        }
        sql.ORDER_BY("create_time desc,update_time desc");
        return sql.toString();

    }

    //topic表update操作SQL
    public String updateTopicByIdProvider(Topic topic) {
        SQL sql = new SQL();
        sql.UPDATE("topic");
        if (topic.getTitle() != null) {
            sql.SET("title = #{title}");
        }
        if (topic.getId() != null) {
            sql.SET("id = #{id}");
        }
        if (topic.getContent() != null) {
            sql.SET("content = #{content}");
        }
        if (topic.getTagId() != null) {
            sql.SET("tag_id = #{tagId}");
        }
        if (topic.getImg() != null) {
            sql.SET("img = #{img}");
        }
        if (topic.getState() != null) {
            sql.SET("state = #{state}");
        }
        if (topic.getUpdaterId() != null) {
            sql.SET("updater_id = #{updaterId}");
        }
        if (topic.getUpdateTime() != null) {
            sql.SET("update_time = #{updateTime}");
        }
        sql.WHERE(" id = #{id}");
        return sql.toString();
    }

    //topic表add操作SQL
    public String addTopicProvider(Topic topic) {
        SQL sql = new SQL();
        sql.INSERT_INTO("topic");
        sql.INTO_COLUMNS("id", "title", "content", "tag_id", "creator_id", "create_time");
        sql.INTO_VALUES("#{id}", "#{title}", "#{content}", "#{tagId}", "#{creatorId}", "#{createTime}");
        if (topic.getImg() != null) {
            sql.INTO_COLUMNS("img");
            sql.INTO_VALUES("#{img}");
        }
        if (topic.getUpdateTime() != null) {
            sql.INTO_COLUMNS("update_time");
            sql.INTO_VALUES("#{updateTime}");
        }
        if (topic.getUpdaterId() != null) {
            sql.INTO_COLUMNS("updater_id");
            sql.INTO_VALUES("#{updaterId}");
        }
        if (topic.getState() != null) {
            sql.INTO_COLUMNS("state");
            sql.INTO_VALUES("#{state}");
        }
        if (topic.getDelFlag() != null) {
            sql.INTO_COLUMNS("del_flag");
            sql.INTO_VALUES("#{delFlag}");
        }

        return sql.toString();
    }

    //user表update操作SQL
    public String updateByLoginNameProvider(User user) {
        SQL sql = new SQL();
        sql.UPDATE("user");
        if (user.getName() != null) {
            sql.SET("name = #{name}");
        }
        if (user.getTel() != null) {
            sql.SET("tel = #{tel}");
        }
        if (user.getAddress() != null) {
            sql.SET("address = #{address}");
        }
        if (user.getPassword() != null) {
            sql.SET("password = #{password}");
        }
        if (user.getState() != null) {
            sql.SET("state = #{state}");
        }
        if (user.getId() != null) {
            sql.SET("id = #{id}");
        }
        if (user.getDelFlag() != null) {
            sql.SET("del_flag = #{delFlag}");
        }
        if (user.getCreateTime() != null) {
            sql.SET("create_time = #{createTime}");
        }
        if (user.getImg() != null) {
            sql.SET("img = #{img}");
        }
        sql.WHERE(" login_name = #{loginName}");
        return sql.toString();
    }

    //topic表getTopicRespById操作SQL
    public String getTopicRespByIdProvider(String id) {
        SQL sql = new SQL();
        sql.SELECT("t.*,u.login_name creator_login_name,tag.tag_name");
        sql.FROM("topic t,user u,tag");
        sql.WHERE("t.creator_id = u.id AND t.tag_id=tag.id and t.del_flag = '0'");
        sql.WHERE("t.id= #{id}");
        return sql.toString();
    }

    //管理员countProvider操作SQL
    public String countProvider(String query,String type) {
        String sql="";
        switch (type) {
            case "UnverifiedUser": {
                sql = "select count(1) from user where state = '0' and del_flag = '0'";
                break;
            }
            case "userList": {
                sql = "select count(1) from user where state != '0' and (name like '%${query}%' or " +
                        "login_name like '%${query}%')  and del_flag = '0'";
                break;
            }
            case "topicList": {
                sql = "select count(1) from topic t,user u,tag " +
                        "where t.creator_id = u.id AND t.tag_id=tag.id and t.del_flag = '0' " +
                        "and (t.title like '%${query}%' or tag.tag_name like '%${query}%' or u.login_name like " +
                        "'%${query}%')";
                break;
            }
            case "tagList": {
                sql = "select count(1) from tag where del_flag = '0'";
                break;
            }
        }
        return sql;
    }



}
